﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using PracticeAzure.Website.Shared;
using System.Web.Mvc;

namespace PracticeAzure.Website.Data
{
    public class Corrigendum
    {
        public long ID { get; set; }
        public string Title { get; set; }
        public string Body { get; set; }
        public int ReplyCount { get; set; }
        public DateTime CreatedOn { get; set; }
        public DateTime UpdatedOn { get; set; }

        public MvcHtmlString RenderedBody
        {
            get
            {
                var html = Body;
                html = RenderEngine.Current.Render(html);
                return MvcHtmlString.Create(html);
            }
        }

        public static Corrigendum Load(long id)
        {
            Corrigendum corrigendum = null;
            using (var conn = SqlHelperLite.CreateSqlConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "LoadCorrigendum";
                cmd.Parameters.Add(new SqlParameter("@id", id));
                conn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        corrigendum = new Corrigendum()
                        {
                            ID = (long)reader["ID"],
                            Title = (string)reader["Title"],
                            Body = (string)reader["Body"],
                            ReplyCount = (int)reader["ReplyCount"],
                            CreatedOn = (DateTime)reader["CreatedOn"],
                            UpdatedOn = (DateTime)reader["UpdatedOn"]
                        };
                    }
                }
            }
            return corrigendum;
        }

        public static void Delete(long id)
        {
            using (var conn = SqlHelperLite.CreateSqlConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "DeleteCorrigendum";
                cmd.Parameters.Add(new SqlParameter("@id", id));
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

        public static void Erase(long id)
        {
            using (var conn = SqlHelperLite.CreateSqlConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "EraseCorrigendum";
                cmd.Parameters.Add(new SqlParameter("@id", id));
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

        public void Save()
        {
            using (var conn = SqlHelperLite.CreateSqlConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                if (ID > 0)
                {
                    // update an existing corrigendum
                    cmd.CommandText = "UpdateCorrigendum";
                    cmd.Parameters.Add(new SqlParameter("@id", ID));
                }
                else
                {
                    // create a new corrigendum
                    cmd.CommandText = "CreateCorrigendum";
                    cmd.Parameters.AddReturnValue();
                }
                cmd.Parameters.Add(new SqlParameter("@title", Title));
                cmd.Parameters.Add(new SqlParameter("@body", Body));
                conn.Open();
                cmd.ExecuteNonQuery();
                // set the id if create a new corrigendum
                if (ID <= 0)
                {
                    ID = (long)(int)cmd.GetReturnValue();
                }
            }
        }

        public static IEnumerable<Corrigendum> List(int? page)
        {
            var corrigendums = new List<Corrigendum>();
            using (var conn = SqlHelperLite.CreateSqlConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "ListCorrigendums";
                cmd.Parameters.Add(new SqlParameter("@pageIndex", page.GetValueOrDefault(1)));
                cmd.Parameters.Add(new SqlParameter("@recordsPerPage", GlobalConfiguration.PageSize));
                conn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        corrigendums.Add(new Corrigendum()
                        {
                            ID = (long)reader["ID"],
                            Title = (string)reader["Title"],
                            Body = ((string)reader["Body"]).ToBio(),
                            ReplyCount = (int)reader["ReplyCount"],
                            CreatedOn = (DateTime)reader["CreatedOn"],
                            UpdatedOn = (DateTime)reader["UpdatedOn"]
                        });
                    }
                }
            }
            return corrigendums;
        }

        public static long Count()
        {
            using (var conn = SqlHelperLite.CreateSqlConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT COUNT(ID) FROM Corrigendum WHERE IsDeleted = 0";
                conn.Open();
                return (long)(int)cmd.ExecuteScalar();
            }
        }

        public static void Reply(long id, string reply, string email)
        {
            using (var conn = SqlHelperLite.CreateSqlConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "ReplyCorrigendum";
                cmd.Parameters.Add(new SqlParameter("@id", id));
                cmd.Parameters.Add(new SqlParameter("@body", reply));
                cmd.Parameters.Add(new SqlParameter("@email", email));
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }
}